import pymysql 

#连接数据库
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = '123456',
    db = 'feizhi',
    charset = 'utf8'
)

#创建游标
cursor = conn.cursor()

#定义创建部门表的SQL语句
create_dep = '''create table departments(
dep_id int, dep_name varchar(50),
primary key(dep_id)
)'''
#定义创建工资表的语句，与员工标有外键约束
create_emp = '''create table employees(
emp_id int, emp_name varchar(50), email varchar(50), dep_id int,
primary key(emp_id), foreign key(dep_id) references departments(dep_id)
)'''
#定义创建工资表的语句，与员工表有外键约束
create_sal = '''create table salary(
id int, date date, emp_id int, basic int, awards int,
primary key(id), foreign key(emp_id) references employees(emp_id)
)'''

#通过游标执行SQL语句
cursor.execute(create_dep)
cursor.execute(create_emp)
cursor.execute(create_sal)

#确认更改
conn.commit()

#关闭游标，关闭数据库连接
cursor.close()
conn.close()
